package org.apogee.mil.server.database.helper;

import org.apogee.mil.shared.Constants.QuestionFields;

public class DatabaseHelper {
	
	public static String createUpdateStatement(String table, String[] fields, String[] values, String whereClause) {
		StringBuilder sb = new StringBuilder("UPDATE " + table + " SET ");

		for (int i = 0; i < values.length; i++) {
			String currentField = fields[i];
			String currentValue = values[i];
			if (currentValue.toLowerCase().equals("sysdate") || (currentValue.toLowerCase().contains("nextval"))){
				sb.append(currentField + "=" +currentValue);	
			}
			else if (currentValue.trim().equals("") || currentValue.toLowerCase().equals("null")) {
				sb.append(currentField + "=NULL");
			}
			else {
				try {
					int val = Integer.parseInt(currentValue);
					sb.append(currentField + "="+val);
				} catch (NumberFormatException ex) {
					try {
						float val = Float.parseFloat(currentValue);
						sb.append(currentField +"="+val);
					} catch (NumberFormatException e) {
						String tmpVal = currentValue.replaceAll("'", "\\");
						currentValue = "'" + tmpVal + "'";
						sb.append(currentField + "=" + currentValue);
					}
				}
			}


			if (i != values.length - 1) {
				sb.append(", ");
			}
		}

		sb.append(" " + whereClause);

		return sb.toString();
	}


	public static String createInsertStatement(String table, String[] fields, String[] values) {

		StringBuilder sb = new StringBuilder("insert into " + table + " (");

		for (int i = 0; i < fields.length; i++) {
			String currentField = fields[i];	
			sb.append(currentField);

			if (i != fields.length - 1) {
				sb.append(", ");
			}
		}

		sb.append(") values (");

		for (int i = 0; i < values.length; i++) {
			String currentValue = values[i];

			if (currentValue.toLowerCase().equals("sysdate") || (currentValue.toLowerCase().contains("nextval"))){
				sb.append(currentValue);	
			}
			else if (currentValue.trim().equals("") || currentValue.toLowerCase().equals("null")) {
				sb.append("NULL");
			}
			else {
				try {
					int val = Integer.parseInt(currentValue);
					sb.append(val);
				} catch (NumberFormatException ex) {
					try {
						float val = Float.parseFloat(currentValue);
						sb.append(val);
					} catch (NumberFormatException e) {
						String tmpVal = currentValue.replaceAll("'", "\\");
						currentValue = "'" + tmpVal + "'";
						sb.append(currentValue);
					}
				}
			}


			if (i != values.length - 1) {
				sb.append(", ");
			}
		}

		sb.append(")");

		return sb.toString();
	}


	public static String getQuestions(String test, int volume, int reviewType) {
		try {
			Integer.parseInt(test.substring(0,1));
			test = "A"+test;
		} catch (NumberFormatException ex) { }

		String stmt = String.format("select %s, %s, %s, %s, %s, %s, %s, %s, %s, %s from %s", 
									QuestionFields.ID, QuestionFields.QUESTION,
									QuestionFields.CHOICE_1, QuestionFields.CHOICE_2, QuestionFields.CHOICE_3,
									QuestionFields.CHOICE_4, QuestionFields.ANSWER, QuestionFields.VOLUME, QuestionFields.REVIEW_OR_TEST,
									QuestionFields.ENABLED, test);

		if (volume == 0) {
			switch (reviewType) {
			case 1: 
				break;
			case 2:
				stmt += String.format(" where %s = 'T'", QuestionFields.REVIEW_OR_TEST);
				break;
			case 3:
				stmt += String.format(" where %s = 'R'", QuestionFields.REVIEW_OR_TEST);
				break;
			}

		} else {
			switch (reviewType) {
			case 1:
				stmt += String.format(" where %s = %d", QuestionFields.VOLUME, volume);
				break;
			case 2:
				stmt += String.format(" where %s = 'T' and volume = %d", QuestionFields.REVIEW_OR_TEST, volume);
				break;
			case 3:
				stmt += String.format(" where %s = 'T' and volume = %d", QuestionFields.REVIEW_OR_TEST, volume);
				break;
			}
		}

		return stmt;
	}

	public static String getSelectedQuestions(String test, int volume, int reviewType) {
		try {
			Integer.parseInt(test.substring(0,1));
			test = "A"+test;
		} catch (NumberFormatException ex) { }

		String stmt = "select id, question, choice1, choice2, choice3, choice4, answer, enabled from " + test;

		if (volume == 0) {
			switch (reviewType) {
			case 1:
				stmt += String.format(" where %s='Y'", QuestionFields.ENABLED); 
				break;
			case 2:
				stmt += String.format(" where %s='Y' and %s='T'", QuestionFields.ENABLED, QuestionFields.REVIEW_OR_TEST);
				break;
			case 3:
				stmt += String.format(" where %s='Y' and %s='R'", QuestionFields.ENABLED, QuestionFields.REVIEW_OR_TEST);
				break;
			}

		} else {
			switch (reviewType) {
			case 1:
				stmt += String.format(" where %s='Y' and %s=%d",  QuestionFields.ENABLED, QuestionFields.VOLUME, volume);
				break;
			case 2:
				stmt += String.format(" where %s='Y' and %s='T' and %s=%d", QuestionFields.ENABLED, 
											QuestionFields.REVIEW_OR_TEST, QuestionFields.VOLUME, volume);
				break;
			case 3:
				stmt += String.format(" where %s='Y' and %s='R' and %s=%d", QuestionFields.ENABLED, 
						QuestionFields.REVIEW_OR_TEST, QuestionFields.VOLUME, volume);
				break;
			}
		}

		return stmt;
	}

}
